/**
 * Copyright 2019 吉鼎科技.

 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.easyplatform.dao.impl;

import cn.easyplatform.dao.DaoException;
import cn.easyplatform.dao.EntityCallback;
import cn.easyplatform.dao.IdentityDao;
import cn.easyplatform.dao.utils.DaoUtils;
import cn.easyplatform.dao.utils.SqlUtils;
import cn.easyplatform.dos.FieldDo;
import cn.easyplatform.dos.OrgDo;
import cn.easyplatform.dos.UserDo;
import cn.easyplatform.entities.BaseEntity;
import cn.easyplatform.entities.beans.task.TaskBean;
import cn.easyplatform.lang.Nums;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.lang.Times;
import cn.easyplatform.messages.vos.MenuNodeVo;
import cn.easyplatform.messages.vos.MenuVo;
import cn.easyplatform.messages.vos.OrgVo;
import cn.easyplatform.messages.vos.RoleVo;
import cn.easyplatform.transaction.jdbc.JdbcTransactions;
import cn.easyplatform.type.DeviceType;
import cn.easyplatform.type.FieldType;
import cn.easyplatform.utils.SerializationUtils;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;
import java.util.*;

/**
 * @author <a href="mailto:davidchen@epclouds.com">littleDog</a> <br/>
 * @since 2.0.0 <br/>
 */
public abstract class AbstractIdentityDao extends AbstractDao implements
        IdentityDao {

    /**
     * @param ds
     */
    public AbstractIdentityDao(DataSource ds) {
        super(ds);
    }

    @Override
    public <T extends BaseEntity> List<RoleVo> getUserRoles(String userId,
                                                            String deviceType, EntityCallback<T> cb) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select a.roleId,a.name,a.image from sys_role_info a inner join sys_user_role_info b on a.roleId=b.roleId and b.userId=? order by b.orderNo");
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            List<RoleVo> roles = new ArrayList<>();
            while (rs.next()) {
                RoleVo vo = new RoleVo();
                vo.setId(rs.getString(1));
                vo.setName(cb.getLabel(rs.getString(2)));
                vo.setImage(rs.getString(3));
                vo.setMenus(getRoleMenu(conn, vo.getId(), deviceType, cb));
                roles.add(vo);
            }
            return roles;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserRoles", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public <T extends BaseEntity> List<RoleVo> getUserRoles(String orgId,
                                                            String userId, String deviceType, EntityCallback<T> cb) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select t.roleId,t.name,t.image from (select a.roleId,a.name,a.image,b.orderNo from sys_role_info a inner join sys_user_role_info b on a.roleId=b.roleId and b.userId=?) t where (t.roleId not in(select a.roleId from sys_org_role_info a)) or (t.roleId in (select a.roleId from sys_org_role_info a where a.orgId=?)) order by t.orderNo");
            pstmt.setString(1, userId);
            pstmt.setString(2, orgId);
            rs = pstmt.executeQuery();
            List<RoleVo> roles = new ArrayList<>();
            while (rs.next()) {
                RoleVo vo = new RoleVo();
                vo.setId(rs.getString(1));
                if (cb != null) {
                    vo.setImage(rs.getString(3));
                    vo.setName(cb.getLabel(rs.getString(2)));
                    vo.setMenus(getRoleMenu(conn, vo.getId(), deviceType, cb));
                }
                roles.add(vo);
            }
            return roles;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserRoles", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public <T extends BaseEntity> List<RoleVo> getUserOrgRoles(String orgId,
                                                               String userId, String deviceType, EntityCallback<T> cb) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select a.roleId,a.name,a.image  from sys_role_info a left join sys_user_org_role_info b on  a.roleId = b.roleId where b.userId=? and b.orgId=? order by b.orderNo");
            pstmt.setString(1, userId);
            pstmt.setString(2, orgId);
            rs = pstmt.executeQuery();
            List<RoleVo> roles = new ArrayList<>();
            while (rs.next()) {
                RoleVo vo = new RoleVo();
                vo.setId(rs.getString(1));
                if (cb != null) {
                    vo.setImage(rs.getString(3));
                    vo.setName(cb.getLabel(rs.getString(2)));
                    vo.setMenus(getRoleMenu(conn, vo.getId(), deviceType, cb));
                }
                roles.add(vo);
            }
            return roles;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserRoles", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    /**
     * @param conn
     * @param roleId
     * @return
     */
    private <T extends BaseEntity> List<MenuVo> getRoleMenu(Connection conn,
                                                            String roleId, String deviceType, EntityCallback<T> cb) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            if (DeviceType.AJAX.getName().equals(deviceType))
                pstmt = conn
                        .prepareStatement("select a.menuId,a.name,a.image,a.tasks from sys_menu_info a inner join sys_role_menu_info b on a.menuId=b.menuId and b.roleId=? and (ISNULL(b.type) or b.type=?) order by b.orderNo");
            else
                pstmt = conn
                        .prepareStatement("select a.menuId,a.name,a.image,a.tasks from sys_menu_info a inner join sys_role_menu_info b on a.menuId=b.menuId and b.roleId=? and b.type=? order by b.orderNo");
            pstmt.setString(1, roleId);
            pstmt.setString(2, deviceType);
            rs = pstmt.executeQuery();
            List<MenuVo> menus = new ArrayList<MenuVo>();
            while (rs.next()) {
                MenuVo menu = new MenuVo();
                menu.setId(rs.getString(1));
                menu.setName(cb.getLabel(rs.getString(2)));
                menu.setImage(rs.getString(3));
                String value = rs.getString(4);
                if (!Strings.isBlank(value)) {
                    String[] tasks = value.trim().split("\\,");
                    if (tasks.length > 0) {
                        List<MenuNodeVo> taskList = new ArrayList<MenuNodeVo>();
                        for (String task : tasks) {
                            if (Strings.isBlank(task))
                                continue;
                            BaseEntity entity = cb.getEntity(task);
                            if (entity == null || !(entity instanceof TaskBean))
                                continue;
                            MenuNodeVo tv = new MenuNodeVo();
                            tv.setId(entity.getId());
                            tv.setImage(((TaskBean) entity).getImage());
                            tv.setName(cb.getLabel(entity.getName()));
                            tv.setDesp(entity.getDescription());
                            taskList.add(tv);
                        }
                        menu.setTasks(taskList);
                    }
                }
                menu.setChildMenus(getMenuChildren(conn, menu.getId(), cb));
                menus.add(menu);
            }
            return menus;
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    /**
     * @param conn
     * @param menuId
     * @param cb
     * @return
     * @throws SQLException
     */
    private <T extends BaseEntity> List<MenuVo> getMenuChildren(
            Connection conn, String menuId, EntityCallback<T> cb)
            throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn
                    .prepareStatement("select menuId,name,image,tasks from sys_menu_info where parentMenuId=? order by orderNo");
            pstmt.setString(1, menuId);
            rs = pstmt.executeQuery();
            List<MenuVo> menus = new ArrayList<MenuVo>();
            while (rs.next()) {
                MenuVo menu = new MenuVo();
                menu.setId(rs.getString(1));
                menu.setName(cb.getLabel(rs.getString(2)));
                menu.setImage(rs.getString(3));
                if (!Strings.isBlank(rs.getString(4))) {
                    String[] tasks = rs.getString(4).split("\\,");
                    if (tasks.length > 0) {
                        List<MenuNodeVo> taskList = new ArrayList<MenuNodeVo>();
                        for (String task : tasks) {
                            if (Strings.isBlank(task))
                                continue;
                            BaseEntity entity = cb.getEntity(task);
                            if (entity == null || !(entity instanceof TaskBean))
                                continue;
                            MenuNodeVo tv = new MenuNodeVo();
                            tv.setImage(((TaskBean) entity).getImage());
                            tv.setId(entity.getId());
                            tv.setName(cb.getLabel(entity.getName()));
                            tv.setDesp(entity.getDescription());
                            taskList.add(tv);
                        }
                        menu.setTasks(taskList);
                    }
                }
                menu.setChildMenus(getMenuChildren(conn, menu.getId(), cb));
                menus.add(menu);
            }
            return menus;
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public <T extends BaseEntity> List<OrgVo> getUserOrgs(String userId,
                                                          EntityCallback<T> cb) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select a.orgId,a.name from sys_org_info a inner join sys_user_org_info b on a.orgId=b.orgId and b.userId=? order by a.orgId");
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            List<OrgVo> orgs = new ArrayList<OrgVo>();
            while (rs.next()) {
                OrgVo org = new OrgVo();
                org.setId(rs.getString(1));
                org.setName(cb.getLabel(rs.getString(2)));
                orgs.add(org);
            }
            return orgs;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserOrgs", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public OrgDo getUserOrg(String query, String orgId) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, orgId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                OrgDo org = new OrgDo();
                org.setId(orgId);
                org.setName(rs.getString(1));
                org.setExtraInfo(new HashMap<String, Object>());
                // 以下为系统变量
                ResultSetMetaData rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();
                for (int i = 1; i <= cols; i++) {
                    String label = null;
                    if (rsmd.getColumnLabel(i) != null)
                        label = rsmd.getColumnLabel(i);
                    else
                        label = rsmd.getColumnName(i);
                    if (!label.startsWith("v") && !label.startsWith("V"))
                        throw new DaoException("dao.org.variable.invalid",
                                label);
                    int no = Nums.toInt(label.substring(1), 0);
                    if (no == 0)
                        throw new DaoException("dao.org.variable.invalid",
                                label);
                    org.getExtraInfo().put(label.substring(1), rs.getObject(i));
                }
                return org;
            }
            return null;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserOrg", ex, orgId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public UserDo getUser(String query, String userId) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(query);
            int count = StringUtils.countMatches(query, "?");
            for (int i = 1; i <= count; i++)
                pstmt.setString(i, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                UserDo user = new UserDo();
                user.setId(userId);
                user.setName(rs.getString(1));
                user.setPassword(rs.getString(2));
                user.setExtraInfo(new HashMap<String, Object>());
                // 以下为系统变量name,password除外
                ResultSetMetaData rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();
                for (int i = 3; i <= cols; i++) {
                    String label = null;
                    if (rsmd.getColumnLabel(i) != null)
                        label = rsmd.getColumnLabel(i);
                    else
                        label = rsmd.getColumnName(i);
                    if (!label.startsWith("v") && !label.startsWith("V"))
                        throw new DaoException("dao.user.variable.invalid",
                                label);
                    int no = Nums.toInt(label.substring(1), 0);
                    if (no == 0)
                        throw new DaoException("dao.user.variable.invalid",
                                label);
                    FieldType type = SqlUtils.getType(label, rsmd.getColumnType(i));
                    if (type == FieldType.BLOB || type == FieldType.OBJECT)
                        user.getExtraInfo()
                                .put(label.substring(1), SerializationUtils.deserialize(rs.getBytes(i)));
                    else
                        user.getExtraInfo()
                                .put(label.substring(1), rs.getObject(i));
                }
                return user;
            }
            return null;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUser", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public List<String[]> getAgent(String query, List<FieldDo> params) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(query);
            Iterator<FieldDo> itr = params.iterator();
            int index = 1;
            while (itr.hasNext()) {
                SqlUtils.setValue(pstmt, index, itr.next());
                index++;
            }
            rs = pstmt.executeQuery();
            List<String[]> result = new ArrayList<String[]>();
            while (rs.next()) {
                String[] user = new String[2];
                user[0] = rs.getString(1);
                user[1] = rs.getString(2);
                result.add(user);
            }
            return result;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUser", ex, params.get(0)
                    .getValue());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public <T extends BaseEntity> List<RoleVo> getRole(String query,
                                                       List<FieldDo> params, EntityCallback<T> cb) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(query);
            Iterator<FieldDo> itr = params.iterator();
            int index = 1;
            while (itr.hasNext()) {
                SqlUtils.setValue(pstmt, index, itr.next());
                index++;
            }
            rs = pstmt.executeQuery();
            List<RoleVo> result = new ArrayList<RoleVo>();
            while (rs.next()) {
                RoleVo rv = new RoleVo();
                rv.setId(rs.getString(1));
                rv.setName(cb.getLabel(rs.getString(2)));
                rv.setImage(rs.getString(3));
                result.add(rv);
            }
            return result;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUser", ex, params.get(0)
                    .getValue());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public <T extends BaseEntity> List<MenuVo> getRoleMenu(String roleId, String deviceType,
                                                           EntityCallback<T> cb) {
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            return getRoleMenu(conn, roleId, deviceType, cb);
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUserRoles", ex, roleId);
        }
    }

    @Override
    public int getLoginFailedTimes(String userId) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select loginFailedTimes from sys_user_info where userId=?");
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next())
                return rs.getInt(1);
            return 0;
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUser", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public void setUser(UserDo user) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select type,validDate,validDays,validstartdate,validenddate from sys_user_info where userId=?");
            pstmt.setString(1, user.getId());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user.setType(rs.getInt(1));
                user.setValidDate(rs.getDate(2));
                user.setValidDays(rs.getInt(3));
                user.setValidStartDate(rs.getDate(4));
                user.setValidEndDate(rs.getDate(5));
            }
        } catch (SQLException ex) {
            throw new DaoException("dao.access.getUser", ex, user.getId());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public void updateLoginFailedTimes(String userId, int times, String ip) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("update sys_user_info set loginFailedTimes=?,lastLoginTime=?,ipAddress=? where userId=?");
            pstmt.setInt(1, times);
            pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            pstmt.setString(3, ip);
            pstmt.setString(4, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            log.error("update", ex);
            throw new DaoException("dao.access.getUser", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updateState(String userId, String address, int state) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            if (state == UserDo.STATE_LOCK) {
                pstmt = conn
                        .prepareStatement("update sys_user_info set state=? where userId=?");
                pstmt.setInt(1, state);
                pstmt.setString(2, userId);
            } else if (address != null) {
                pstmt = conn
                        .prepareStatement("update sys_user_info set lastLoginTime=?,ipAddress=?,loginFailedTimes=0 where userId=?");
                pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                pstmt.setString(2, address);
                pstmt.setString(3, userId);
            } else {
                pstmt = conn
                        .prepareStatement("update sys_user_info set lastLogoutTime=? where userId=?");
                pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                pstmt.setString(2, userId);
            }
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            log.error("update", ex);
            throw new DaoException("dao.access.getUser", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void updatePassword(String userId, String password) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("update sys_user_info set password=?,validDate=? where userId=?");
            pstmt.setString(1, password);
            Date date = Times.toDay();
            pstmt.setDate(2, new java.sql.Date(date.getTime()));
            pstmt.setString(3, userId);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            log.error("update", ex);
            throw new DaoException("dao.access.getUser", ex, userId);
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public Map<String, Object> selectOne(String statement, Object... parameter) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            for (int i = 1; i <= parameter.length; i++)
                pstmt.setObject(i, parameter[i - 1]);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int size = rsmd.getColumnCount();
                Map<String, Object> data = new HashMap<>(size);
                for (int index = 1; index <= size; index++)
                    data.put(rsmd.getColumnName(index).toLowerCase(), rs.getObject(index));
                return data;
            } else
                return null;
        } catch (SQLException ex) {
            log.error("selectOne", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(rs);
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public String getAccess(String roleId, String taskId, String type) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement("SELECT access FROM sys_role_access_info WHERE roleId=? AND taskId=? AND type=?");
            pstmt.setString(1, roleId);
            pstmt.setString(2, taskId);
            pstmt.setString(3, type);
            rs = pstmt.executeQuery();
            if (rs.next())
                return rs.getString(1);
            else
                return null;
        } catch (SQLException ex) {
            log.error("getAccess", ex);
            return null;
        } finally {
            DaoUtils.closeQuietly(rs);
            DaoUtils.closeQuietly(pstmt);
        }
    }
}
